-- More of steadily chipping away at the use of uniqueidentifier...
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
GO
IF ((SELECT t.name FROM sys.columns c
		JOIN sys.types t ON t.system_type_id = c.system_type_id
		WHERE c.object_id = OBJECT_ID('RecordSet.ApplicationProperty')
			AND c.name = 'ID') = 'uniqueidentifier') BEGIN
	CREATE TABLE RecordSet.Tmp_ApplicationProperty (
		ID int NOT NULL IDENTITY (1, 1),
		RecordSetID uniqueidentifier NOT NULL,
		[Key] varchar(30) NOT NULL,
		Value varchar(MAX) NULL
	)

	IF EXISTS (SELECT * FROM RecordSet.ApplicationProperty) BEGIN
		 EXEC('INSERT INTO RecordSet.Tmp_ApplicationProperty (RecordSetID, [Key], Value)
			SELECT RecordSetID, [Key], Value FROM RecordSet.ApplicationProperty WITH (HOLDLOCK TABLOCKX)')
	END

	EXEC ('DROP TABLE RecordSet.ApplicationProperty')
	EXECUTE sp_rename N'RecordSet.Tmp_ApplicationProperty', N'ApplicationProperty', 'OBJECT' 

	ALTER TABLE RecordSet.ApplicationProperty ADD CONSTRAINT PK_RecordSet_ApplicationProperty PRIMARY KEY CLUSTERED  (ID ASC)
	CREATE UNIQUE NONCLUSTERED INDEX IX_RecordSet_ApplicationProperty_Key ON RecordSet.ApplicationProperty (RecordSetID, [Key])
END
GO
IF ((SELECT t.name FROM sys.columns c
		JOIN sys.types t ON t.system_type_id = c.system_type_id
		WHERE c.object_id = OBJECT_ID('Common.ApplicationProperty')
			AND c.name = 'ID') = 'uniqueidentifier') BEGIN
	CREATE TABLE Common.Tmp_ApplicationProperty (
		ID int NOT NULL IDENTITY (1, 1),
		[Key] varchar(30) NOT NULL,
		Value varchar(MAX) NULL
	)

	IF EXISTS (SELECT * FROM Common.ApplicationProperty) BEGIN
		 EXEC('INSERT INTO Common.Tmp_ApplicationProperty ([Key], Value)
			SELECT [Key], Value FROM Common.ApplicationProperty WITH (HOLDLOCK TABLOCKX)')
	END
	EXEC ('DROP TABLE Common.ApplicationProperty')
	EXECUTE sp_rename N'Common.Tmp_ApplicationProperty', N'ApplicationProperty', 'OBJECT' 

	ALTER TABLE Common.ApplicationProperty ADD CONSTRAINT PK_Common_ApplicationProperty PRIMARY KEY CLUSTERED (ID ASC)
	CREATE UNIQUE NONCLUSTERED INDEX IX_Common_ApplicationProperty_Key ON Common.ApplicationProperty ([Key])
END
GO
COMMIT TRANSACTION
GO
ALTER PROCEDURE [Common].[ApplicationProperty_Edit]
	@Key varchar(30)
	,@Value varchar(MAX)
AS
BEGIN
	SET NOCOUNT ON

	IF NOT EXISTS (SELECT * FROM Common.ApplicationProperty ap WHERE ap.[Key] = @Key) BEGIN
		INSERT INTO Common.ApplicationProperty ([Key], Value)
		VALUES (@Key, @Value)
	END
	ELSE BEGIN
		UPDATE Common.ApplicationProperty
			SET Value = @Value
			WHERE [Key] = @Key
	END
END
GO
ALTER PROCEDURE [RecordSet].[ApplicationProperty_Edit]
	@RecordSetID uniqueidentifier
	,@Key varchar(30)
	,@Value varchar(MAX)
AS
BEGIN
	SET NOCOUNT ON

	IF NOT EXISTS (SELECT * FROM RecordSet.ApplicationProperty ap WHERE ap.RecordSetID = @RecordSetID AND ap.[Key] = @Key) BEGIN
		INSERT INTO RecordSet.ApplicationProperty (RecordSetID, [Key], Value)
		VALUES (@RecordSetID, @Key, @Value)
	END
	ELSE BEGIN
		UPDATE RecordSet.ApplicationProperty
			SET Value = @Value
			WHERE RecordSetID = @RecordSetID
				AND [Key] = @Key
	END
END
GO

-- Trying to come up with an improved IO for Pilgrimage data files

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Gene].[Gene_LogHistory] ON [Gene].[Gene]
	AFTER INSERT, UPDATE
AS
BEGIN

	DECLARE @IsInsert bit = 0
			,@OriginalTaxonomyID int
			,@OriginalTaxonomy varchar(MAX)
			,@Taxonomy varchar(MAX)
			,@TaxonomyID int = NULL
			,@GeneID uniqueidentifier

	SELECT @IsInsert = CAST((CASE WHEN d.ID IS NULL THEN 1 ELSE 0 END) AS bit)
			,@OriginalTaxonomyID = d.TaxonomyID
			,@OriginalTaxonomy = d.Taxonomy
		FROM deleted d
	SELECT  @GeneID = i.ID
			,@Taxonomy = i.Taxonomy
			,@TaxonomyID = i.TaxonomyID
		FROM inserted i

	IF (@IsInsert = 1) BEGIN -- INSERT
		IF (@Taxonomy IS NOT NULL) AND (LTRIM(RTRIM(@Taxonomy)) <> '') BEGIN
			EXEC Taxonomy.Taxon_Parse @Taxonomy, @TaxonomyID OUTPUT
			UPDATE Gene.Gene SET TaxonomyID = @TaxonomyID WHERE ID = @GeneID
		END
	END
	ELSE BEGIN -- UPDATE
		IF (@Taxonomy IS NOT NULL) AND (LTRIM(RTRIM(@Taxonomy)) <> '') AND (@OriginalTaxonomy <> @Taxonomy OR @OriginalTaxonomyID IS NULL) BEGIN
			EXEC Taxonomy.Taxon_Parse @Taxonomy, @TaxonomyID OUTPUT
			UPDATE Gene.Gene SET TaxonomyID = @TaxonomyID WHERE ID = @GeneID
		END
		ELSE IF (@OriginalTaxonomy = @Taxonomy) BEGIN
			SET @TaxonomyID = @OriginalTaxonomyID
		END
	END

	INSERT INTO Gene.GeneHistory (RevisionID, ID, Name, [Definition], SourceID,
									GenBankID, Locus, Accession, Organism, Taxonomy, TaxonomyID,
									Nucleotides, SequenceTypeID, SequenceStart, SequenceEnd, CodingSequenceStart, CodingSequenceEnd,
									[Description], Active, LastUpdatedAt, LastUpdateSourceID)
		SELECT ISNULL((SELECT MAX(RevisionID) FROM Gene.GeneHistory g WHERE g.ID = i.ID), 0) + 1
				,i.ID
				,i.Name
				,i.[Definition]
				,i.SourceID
				,i.GenBankID
				,i.Locus
				,i.Accession
				,i.Organism
				,i.Taxonomy
				,@TaxonomyID
				,i.Nucleotides
				,i.SequenceTypeID
				,i.SequenceStart
				,i.SequenceEnd
				,i.CodingSequenceStart
				,i.CodingSequenceEnd
				,i.[Description]
				,i.Active
				,i.LastUpdatedAt
				,i.LastUpdateSourceID
			FROM inserted i

END
GO
ALTER PROCEDURE [Gene].[Gene_Edit]
	@ID uniqueidentifier = NULL OUTPUT,
	@Name varchar(100),
	@Definition varchar(1000),
	@SourceID int,
	@GenBankID int,
	@Locus varchar(100),
	@Accession varchar(20),
	@Organism varchar(250),
	@Taxonomy varchar(4000),
	@Description varchar(MAX),
	@Nucleotides varchar(MAX),
	@SequenceTypeID int,
	@LastUpdatedAt datetime2(7) = NULL,
	@LastUpdateSourceID int = NULL,
	@AllowOverwrite bit = 0
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @NowTime datetime2(7) = SYSDATETIME()

	IF @LastUpdateSourceID IS NULL BEGIN
		SET @LastUpdateSourceID = @SourceID
	END

	IF NOT EXISTS (SELECT * FROM Gene.Gene g WHERE g.ID = @ID) BEGIN
		IF (@ID IS NULL) BEGIN
			SET @ID = NEWID()
		END

		INSERT INTO Gene.Gene (ID, Name, [Definition], SourceID
								,GenBankID, Locus, Accession, Organism, Taxonomy, [Description]
								,Nucleotides, SequenceTypeID, LastUpdatedAt, LastUpdateSourceID)
		VALUES (@ID, @Name, @Definition, @SourceID
				,@GenBankID, @Locus, @Accession, @Organism, @Taxonomy, @Description
				,@Nucleotides, @SequenceTypeID
				,@LastUpdatedAt, @LastUpdateSourceID)
	END
	ELSE BEGIN
		IF (@AllowOverwrite = 1) BEGIN
			-- Currently not allowing for partial updates (i.e.: ISNULL(@column, column)); whenever a gene is saved and an update is possible all
			-- values must be provided.
			UPDATE Gene.Gene
				SET SourceID = @SourceID
					,Name = @Name
					,[Definition] = @Definition
					,GenBankID = @GenBankID
					,Locus = @Locus
					,Accession = @Accession
					,Organism = @Organism
					,Taxonomy = @Taxonomy
					,[Description] = @Description
					,Nucleotides = @Nucleotides
					,SequenceTypeID = @SequenceTypeID
					,LastUpdatedAt = @LastUpdatedAt
					,LastUpdateSourceID = @LastUpdateSourceID
				WHERE ID = @ID
		END
	END
END
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE o.object_id = OBJECT_ID('Common.ConvertToBase64')) BEGIN
	DROP FUNCTION Common.ConvertToBase64
END
GO
CREATE FUNCTION Common.ConvertToBase64 (@s nvarchar(MAX))
	RETURNS varchar(MAX)
AS
BEGIN

	DECLARE @binary varbinary(MAX) = CAST(@s AS varbinary(MAX))
	RETURN (CAST('' as xml).value('xs:base64Binary(sql:variable("@binary"))', 'varchar(max)'))

END
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE o.object_id = OBJECT_ID('Common.ConvertFromBase64')) BEGIN
	DROP FUNCTION Common.ConvertFromBase64
END
GO
CREATE FUNCTION Common.ConvertFromBase64 (@Encoded varchar(MAX))
	RETURNS nvarchar(MAX)
AS
BEGIN

	RETURN CAST(CAST('' as xml).value('xs:base64Binary(sql:variable("@Encoded"))', 'varbinary(MAX)') AS nvarchar(MAX))

END
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE o.object_id = OBJECT_ID('Common.ConvertXMLToBase64')) BEGIN
	DROP FUNCTION Common.ConvertXMLToBase64
END
GO
CREATE FUNCTION Common.ConvertXMLToBase64 (@x xml)
	RETURNS varchar(MAX)
AS
BEGIN

	RETURN Common.ConvertToBase64(CAST(@x AS nvarchar(MAX)))
	
END
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE o.object_id = OBJECT_ID('Common.ConvertXMLFromBase64')) BEGIN
	DROP FUNCTION Common.ConvertXMLFromBase64
END
GO
CREATE FUNCTION Common.ConvertXMLFromBase64 (@Encoded varchar(MAX))
	RETURNS xml
AS
BEGIN

	RETURN CAST(Common.ConvertFromBase64(@Encoded) AS xml)

END
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE o.object_id = OBJECT_ID('NCBI.ConcatenateBlastNAlignments')) BEGIN
	DROP FUNCTION NCBI.ConcatenateBlastNAlignments
END
GO
CREATE FUNCTION NCBI.ConcatenateBlastNAlignments (@RequestID int)
RETURNS varchar(MAX)
AS
BEGIN
	DECLARE @AlignmentIDs varchar(MAX) = ''

	SELECT @AlignmentIDs += CAST(al.AlignmentID AS varchar(10)) + ';'
		FROM NCBI.BlastNAlignment al
		WHERE al.RequestID = @RequestID

	RETURN @AlignmentIDs	
END
GO

IF EXISTS (SELECT * FROM sys.procedures p WHERE p.object_id = OBJECT_ID('RecordSet.RecordSet_Export2')) BEGIN
	DROP PROCEDURE RecordSet.RecordSet_Export2
END
GO
CREATE PROCEDURE RecordSet.RecordSet_Export2
	@RecordSetID uniqueidentifier
	,@SelectedSubSetIDs Common.ListUniqueIdentifier READONLY
	,@IncludeJobHistory_TargetIDs Common.ListInt READONLY

	,@GeneOptions_IncludeAlignedSequences bit = 1
	,@GeneOptions_IncludeGeneSequenceAnnotations bit = 1
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @SubSetIDs Common.ListUniqueIdentifier

	IF EXISTS (SELECT * FROM @SelectedSubSetIDs) BEGIN
		INSERT INTO @SubSetIDs SELECT Value FROM @SelectedSubSetIDs
	END
	ELSE BEGIN
		-- None means all...
		INSERT INTO @SubSetIDs
		SELECT sub.ID
			FROM RecordSet.SubSet sub
			WHERE sub.RecordSetID = @RecordSetID
				AND sub.Active = 1
	END

	-- To start, get all of the Gene.Gene records that are directly being exported; in other words, everything that's linked in RecordSet.SubSet_Gene
	-- for the subsets that the user selected within the current RecordSet.

	DECLARE @GeneIDs TABLE (GeneID uniqueidentifier)
	INSERT INTO @GeneIDs
	SELECT g.ID
		FROM Gene.Gene g
		JOIN RecordSet.SubSetGene sg ON sg.GeneID = g.ID
		JOIN @SubSetIDs s_id ON s_id.Value = sg.SubSetID
		WHERE g.Active = 1

	IF (@GeneOptions_IncludeAlignedSequences = 1) BEGIN
		-- We also need all of the Gene.Gene records that are aligned with the directly selected sequences.	 These are not restricted by 
		-- RecordSet/SubSet because the app doesn't restrict them that way when you view aligned sequences on frmGeneDetails.
		-- At the moment we're not including Query sequences for which our directly selected sequences were aligned as Subject sequences.
		INSERT INTO @GeneIDs
		SELECT DISTINCT sg.ID
			FROM BlastN.Alignment al
			JOIN @GeneIDs g_id ON g_id.GeneID = al.QueryID
			JOIN Gene.Gene sg ON sg.ID = al.SubjectID
			WHERE sg.Active = 1
				AND NOT EXISTS (SELECT * FROM @GeneIDs ex WHERE ex.GeneID = sg.ID)
	END

	-- Get all of the PAML.Result records that are directly being exported.

	DECLARE @ResultIDs TABLE (ResultID int)
	INSERT INTO @ResultIDs
	SELECT r.ID
		FROM PAML.Result r
		JOIN PAML.SubSetResult sr ON sr.ResultID = r.ID
		JOIN @SubSetIDs s_id ON s_id.Value = sr.SubSetID

	DECLARE @JobIDs TABLE (JobID uniqueidentifier)
	-- Build out the list of jobs to include
	IF EXISTS (SELECT * FROM @IncludeJobHistory_TargetIDs) BEGIN
		IF EXISTS (SELECT *
						FROM @IncludeJobHistory_TargetIDs t_id
						JOIN Job.[Target] t ON t.ID = t_id.Value
						WHERE t.[Key] IN ('BLASTN_NCBI', 'PRANK', 'MUSCLE', 'PhyML')) BEGIN
			INSERT INTO @JobIDs
			SELECT j.ID
				FROM Job.Job j
				JOIN @IncludeJobHistory_TargetIDs t ON t.Value = j.TargetID
				WHERE j.Active = 1
					AND EXISTS (SELECT *
									FROM Job.Gene jg
									JOIN @GeneIDs g_id ON g_id.GeneID = jg.GeneID
									WHERE jg.JobID = j.ID
										AND jg.DirectionID = 1)
		END

		IF EXISTS (SELECT *
						FROM @IncludeJobHistory_TargetIDs t_id
						JOIN Job.[Target] t ON t.ID = t_id.Value
						WHERE t.[Key] IN ('CodeML')) BEGIN
			INSERT INTO @JobIDs
			SELECT j.ID
				FROM Job.Job j
				JOIN @IncludeJobHistory_TargetIDs t ON t.Value = j.TargetID
				WHERE j.Active = 1
					AND EXISTS (SELECT *
									FROM PAML.SubSetResult sr
									JOIN @ResultIDs r_id ON r_id.ResultID = sr.ResultID
									JOIN PAML.Result r ON r.ID = r_id.ResultID
									JOIN PAML.Tree t ON t.ID = r.TreeID
									WHERE t.JobID = j.ID
										AND r.Active = 1)
		END
	END

	DECLARE @Output TABLE (ID int identity(1,1), Data xml)
	
	-- Export some properties of the source database and recordset; this becomes the "header" for the data file.
	INSERT INTO @Output (Data)
	SELECT (SELECT [Properties].Value AS [DatabaseVersion]
					FROM Common.ApplicationProperty [Properties]
					WHERE [Properties].[Key] = 'DatabaseVersion'
					FOR XML AUTO, ELEMENTS)
	UNION ALL
	SELECT (SELECT [RecordSet].*
					,[Properties].[Key]
					,[Properties].Value
				FROM RecordSet.RecordSet [RecordSet]
				JOIN RecordSet.ApplicationProperty [Properties] ON [Properties].RecordSetID = [RecordSet].ID
				WHERE [RecordSet].ID = @RecordSetID
				FOR XML AUTO, ELEMENTS)
	UNION ALL 
	SELECT (SELECT [SubSet].ID
					,[SubSet].Name
					,[SubSet].DataTypeID
					,[SubSet].LastOpenedAt
					,[SubSet].[Open]
					,[SubSet].DisplayIndex
				FROM RecordSet.RecordSet [RecordSet]
				JOIN RecordSet.SubSet [SubSet] ON [SubSet].RecordSetID = [RecordSet].ID
				JOIN @SubSetIDs s_id ON s_id.Value = [SubSet].ID
				ORDER BY [SubSet].Name
				FOR XML AUTO, ROOT ('RecordSet-SubSet'))

	-- Export the Genes, nucleotide sequence data and annotations, and their SubSet assignments
	UNION ALL
	SELECT (SELECT [Gene].ID
					,[Gene].Name
					,[Gene].[Definition]
					,[Gene].SourceID
					,[Gene].GenBankID
					,[Gene].Locus
					,[Gene].Accession
					,[Gene].Organism
					,[Gene].Taxonomy
					,[Gene].Nucleotides
					,[Gene].SequenceTypeID
					,[Gene].[Description]
					,[Gene].LastUpdatedAt
					,[Gene].LastUpdateSourceID
				FROM Gene.Gene [Gene]
				JOIN @GeneIDs g ON g.GeneID = [Gene].ID
				ORDER BY ID
				FOR XML AUTO, ELEMENTS, ROOT ('RecordSet-Gene'))
	UNION ALL
	SELECT (SELECT [Sequence].*
				FROM Gene.NucleotideSequence [Sequence]
				JOIN @GeneIDs g ON g.GeneID = [Sequence].GeneID
				ORDER BY g.GeneID
				FOR XML AUTO, ELEMENTS, ROOT ('RecordSet-Gene-Sequence'))
	UNION ALL
	SELECT (SELECT [SubSet].ID
					,[Gene].GeneID
					,[Gene].ModifiedAt
				FROM RecordSet.SubSet [SubSet]
				JOIN RecordSet.SubSetGene [Gene] ON [Gene].SubSetID = [SubSet].ID
				JOIN @SubSetIDs s_id ON s_id.Value = [SubSet].ID
				JOIN @GeneIDs g ON g.GeneID = [Gene].GeneID
				ORDER BY [SubSet].ID
				FOR XML AUTO, ROOT ('RecordSet-SubSet-Gene'))
	
	IF (@GeneOptions_IncludeGeneSequenceAnnotations = 1) BEGIN
		INSERT INTO @Output (Data)
		SELECT (SELECT [Gene].GeneID
						,[Feature].ID
						,[Feature].[Rank]
						,[Feature].FeatureKeyID
						,[Feature].GeneQualifier
						,[Feature].GeneIDQualifier
						,[Feature-Interval].ID
						,[Feature-Interval].Start
						,[Feature-Interval].[End]
						,[Feature-Interval].IsComplement
						,[Feature-Interval].StartModifier
						,[Feature-Interval].EndModifier
						,[Feature-Interval].Accession
					FROM Gene.Feature [Feature]
					JOIN Gene.FeatureInterval [Feature-Interval] ON [Feature-Interval].FeatureID = [Feature].ID
					JOIN @GeneIDs [Gene] ON [Gene].GeneID = [Feature].GeneID
					ORDER BY [Gene].GeneID, [Feature].ID, [Feature-Interval].ID
					FOR XML AUTO, ROOT ('RecordSet-Gene-Feature'))
	END

	IF EXISTS (SELECT * FROM @IncludeJobHistory_TargetIDs) BEGIN
		INSERT INTO @Output (Data)
		SELECT (SELECT [Job].ID
						,[Job].TargetID
						,[Job].StartedAt
						,[Job].EndedAt
						,[Job].StatusID
						,[Job].SubSetID
						,[Job].Title
						,CAST('<Encoded>' + Common.ConvertXMLToBase64([Job].AdditionalProperties) + '</Encoded>' AS xml) AS AdditionalProperties
						,CAST(('<Output>' + Common.ConvertToBase64(CONVERT(nvarchar(MAX), [Output].OutputText)) + '</Output>') AS xml) AS OutputText
					FROM Job.Job [Job]
					LEFT OUTER JOIN Job.OutputText [Output] ON [Output].JobID = [Job].ID
					JOIN @JobIDs j_id ON j_id.JobID = [Job].ID
					ORDER BY [Job].ID
					FOR XML AUTO, ROOT ('RecordSet-Job'))
		UNION ALL
		SELECT (SELECT [Job].ID
						,[Exception].ID
						,[Exception].RequestID
						,[Exception].ParentID
						,[Exception].ExceptionAt
						,[Exception].ExceptionType
						,Common.ConvertToBase64(CONVERT(nvarchar(MAX), [Exception].[Message])) AS [Message]
						,Common.ConvertToBase64(CONVERT(nvarchar(MAX), [Exception].[Source])) AS [Source]
						,Common.ConvertToBase64(CONVERT(nvarchar(MAX), [Exception].StackTrace)) AS StackTrace
					FROM Job.Job [Job]
					JOIN Job.Exception [Exception] ON [Exception].JobID = [Job].ID
					JOIN @JobIDs j_id ON j_id.JobID = [Job].ID
					ORDER BY [Job].ID, [Exception].ID
					FOR XML AUTO, ROOT ('RecordSet-Job-Exception'))
		UNION ALL
		SELECT (SELECT [Job].ID
						,[Gene].GeneID
						,[Gene].DirectionID
					FROM Job.Gene [Gene]
					JOIN Job.Job [Job] ON [Job].ID = [Gene].JobID
					JOIN @JobIDs j_id ON j_id.JobID = [Job].ID
					ORDER BY [Job].ID, [Gene].GeneID
					FOR XML AUTO, ROOT ('RecordSet-Job-Gene'))
		UNION ALL
		SELECT (SELECT [Request].ID
						,[Request].RequestID
						,[Request].JobID
						,[Request].StartTime
						,[Request].EndTime
						,[Request].LastStatus
						,[Request].LastUpdatedAt
						,[Request].TargetDatabase
						,[Request].[Algorithm]
						,CASE WHEN ISNULL([Request].StatusInformation, '') = '' THEN NULL
							ELSE CAST(('<Text>' + [Request].StatusInformation + '</Text>') AS xml) 
							END AS StatusInformation
						,CAST(('<IDs>' + NCBI.ConcatenateBlastNAlignments([Request].ID) + '</IDs>') AS xml) AS BlastNAlignments
						,[Request-Gene].GeneID
						,[Request-Gene].StatusID
					FROM NCBI.Request [Request]
					JOIN @JobIDs j_id ON j_id.JobID = [Request].JobID
					JOIN NCBI.Gene [Request-Gene] ON [Request-Gene].RequestID = [Request].ID
					ORDER BY [Request].ID, [Request-Gene].GeneID
					FOR XML AUTO, ROOT ('RecordSet-NCBI-Request'))
		UNION ALL
		SELECT (SELECT DISTINCT [Alignment].*
						,[Alignment-Exon].OrientationID
						,[Alignment-Exon].BitScore
						,[Alignment-Exon].AlignmentLength
						,[Alignment-Exon].IdentitiesCount
						,[Alignment-Exon].Gaps
						,[Alignment-Exon].QueryRangeStart
						,[Alignment-Exon].QueryRangeEnd
						,[Alignment-Exon].SubjectRangeStart
						,[Alignment-Exon].SubjectRangeEnd
					FROM BlastN.Alignment [Alignment]
					JOIN BlastN.AlignmentExon [Alignment-Exon] ON [Alignment-Exon].AlignmentID = [Alignment].ID
					JOIN NCBI.BlastNAlignment n_al ON n_al.AlignmentID = [Alignment].ID
					JOIN NCBI.Request req ON req.ID = n_al.RequestID
					JOIN @JobIDs j_id ON j_id.JobID = req.JobID
					JOIN Gene.Gene qry ON qry.ID = Alignment.QueryID
					JOIN Gene.Gene sbj ON sbj.ID = Alignment.SubjectID
					WHERE qry.Active = 1
						AND sbj.Active = 1
					ORDER BY [Alignment].ID
					FOR XML AUTO, ROOT ('RecordSet-BLASTN-Alignment'))
		--UNION ALL
		--SELECT (SELECT [Request].ID
		--				,[Alignment].AlignmentID
		--				--,(SELECT [Alignment].AlignmentID
		--				--		FROM NCBI.BlastNAlignment [Alignment] 
		--				--		WHERE [Alignment].RequestID = [Request].ID
		--				--		FOR XML PATH(''), TYPE) AS "Alignments"
		--			FROM NCBI.Request [Request]
		--			JOIN NCBI.BlastNAlignment [Alignment] ON [Alignment].RequestID = [Request].ID
		--			JOIN @JobIDs j_id ON j_id.JobID = [Request].JobID
		--			WHERE EXISTS (SELECT * FROM NCBI.BlastNAlignment ex WHERE ex.RequestID = [Request].ID)
		--			FOR XML AUTO, ROOT ('RecordSet-Request-Alignment'))
		UNION ALL
		SELECT (SELECT [Tree].ID
						,[Tree].JobID
						,[Tree].TreeFilePath
						,[Tree].SequencesFilePath
						,[Tree].[Rank]
						,[Tree].StatusID
						,[Tree].Title
						,[Tree].SequenceCount
						,[Tree].SequenceLength
						,CAST('<Encoded>' + Common.ConvertXMLToBase64([Tree].ControlConfiguration) + '</Encoded>' AS xml) AS ControlConfiguration

						,[Config].ID
						,[Config].Model
						,[Config].NCatG
						,CONVERT(varchar(10), [Config].KStart) + '|' + CONVERT(varchar(10), ISNULL([Config].KEnd, [Config].KStart)) + '|' + CONVERT(varchar(10), [Config].KInterval) + '|' + CONVERT(varchar(1), [Config].KFixed) AS K
						,CONVERT(varchar(10), [Config].WStart) + '|' + CONVERT(varchar(10), ISNULL([Config].WEnd, [Config].WStart)) + '|' + CONVERT(varchar(10), [Config].WInterval) + '|' + CONVERT(varchar(1), [Config].WFixed) AS W
						,[Config].[Rank]
						,[Config].StatusID
						,[Config].ModelPresetID
						,PAML.GetNSSitesListForAnalysisConfiguration([Config].ID) AS NSSites
					FROM PAML.Tree [Tree]
					JOIN PAML.AnalysisConfiguration [Config] ON [Config].TreeID = [Tree].ID
					JOIN @JobIDs j_id ON j_id.JobID = [Tree].JobID
					ORDER BY [Tree].ID, [Config].ID
					FOR XML AUTO, ROOT ('RecordSet-PAML-Tree'))
		UNION ALL
		SELECT (SELECT [Result].ID
						,[Result].TreeID
						,[Result].AnalysisConfigurationID
						,[Result].NSSite
						,[Result].Kappa
						,[Result].Omega
						,[Result].np
						,[Result].lnL
						,[Result].k
						,[Result].Duration
						,[Result].CompletedAt
						,CONVERT(xml,
							(SELECT ssr.SubSetID AS ID
								FROM PAML.SubSetResult ssr
								WHERE ssr.ResultID = [Result].ID
								FOR XML RAW)
							) AS SubSetIDs
						,[Value].ID
						,[Value].SiteClass
						,[Value].ValueTypeID
						,[Value].[Rank]
						,[Value].Value
					FROM PAML.Result [Result]
					JOIN PAML.ResultdNdSValue [Value] ON [Value].ResultID = [Result].ID
					JOIN PAML.SubSetResult [SubSetResult] ON [SubSetResult].ResultID = [Result].ID
					JOIN PAML.Tree [Tree] ON [Tree].ID = [Result].TreeID
					JOIN @ResultIDs r_id ON r_id.ResultID = [Result].ID
					WHERE [Result].Active = 1
					ORDER BY [Result].ID, [Value].ID
					FOR XML AUTO, ROOT ('RecordSet-PAML-Result'))
		UNION ALL
		SELECT (SELECT [Output].ID
						,[Output].TreeID
						,[Output].AnalysisConfigurationID
						,[Output].Kappa
						,[Output].Omega
						,[Output].StatusID
						,[Output].ProcessDirectory
						,Common.ConvertToBase64(CONVERT(nvarchar(MAX), [Output].OutputData)) AS OutputData
						,Common.ConvertToBase64(CONVERT(nvarchar(MAX), [Output].ErrorData)) AS ErrorData
					FROM PAML.ProcessOutput [Output]
					JOIN PAML.Tree [Tree] ON [Tree].ID = [Output].TreeID
					JOIN @JobIDs j_id ON j_id.JobID = [Tree].JobID
					ORDER BY [Output].ID
					FOR XML AUTO, ROOT ('RecordSet-PAML-Process'))
		UNION ALL
		SELECT (SELECT [Exception].ExceptionID
						,[Exception].ProcessOutputID
					FROM PAML.ProcessException [Exception]
					JOIN PAML.ProcessOutput o ON o.ID = [Exception].ProcessOutputID
					JOIN PAML.Tree t ON t.ID = o.TreeID
					JOIN @JobIDs j_id ON j_id.JobID = t.JobID
					FOR XML AUTO, ROOT ('RecordSet-PAML-Exception'))
	END

	DECLARE @final xml = '<Pilgrimage />'
			,@fragment xml
	DECLARE @count int = (SELECT COUNT(*) FROM @Output)
	DECLARE @current int = 1

	WHILE @current <= @count BEGIN
		SELECT @fragment = Data
			FROM @Output
			WHERE ID = @current

		SET @final.modify('insert sql:variable("@fragment") into (/Pilgrimage)[1] ')           
	
		SET @current += 1;
	END 
	
	SELECT @final;

	/*
	SELECT *
		FROM @Output
		ORDER BY ID
	*/
END
GO

DECLARE @RecordSetID uniqueidentifier = '18625BD1-D6AB-4074-B977-80DE221253FF'
DECLARE @SubSetIDs Common.ListUniqueIdentifier
INSERT INTO @SubSetIDs SELECT ID FROM RecordSet.SubSet sub WHERE sub.RecordSetID = @RecordSetID AND sub.DataTypeID IN (1,2)

DECLARE @IncludeJobHistory_TargetIDs Common.ListInt
INSERT INTO @IncludeJobHistory_TargetIDs
SELECT t.ID
	FROM Job.[Target] t
	WHERE t.[Key] IN ('CodeML', 'BLASTN_NCBI', 'PRANK', 'MUSCLE', 'PhyML')

--EXEC RecordSet.RecordSet_Export2 @RecordSetID, @SubSetIDs, @IncludeJobHistory_TargetIDs, 1, 1

GO
UPDATE Common.ApplicationProperty
	SET Value = '1.4.5.1'
	WHERE [Key] = 'DatabaseVersion'
GO

--SELECT rs.ID, rs.Name, j.ID, j.Title, po.*
--	FROM PAML.ProcessOutput po
--	JOIN PAML.ProcessException ex ON ex.ProcessOutputID = po.ID
--	JOIN PAML.Tree t ON t.ID = po.TreeID
--	JOIN Job.Job j ON j.ID = t.JobID
--	JOIN RecordSet.RecordSet rs ON rs.ID = j.RecordSetID
